package com.jeeplus.modules.fee.util;

import com.jeeplus.modules.fee.entity.Js;
import com.jeeplus.modules.fee.entity.Profit;
import com.jeeplus.modules.fee.entity.Receivable;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * Created by lichaohui on 2018/4/26.
 */
public  class Exportprofit {


    //利润分析
    public  void  exportprofit(HttpServletResponse response,  List<Profit> list){
        //excel标题
        String[] title = {"项目","装运单号","提货单号","起运地","到站省","到站地","总收入","总支出","税率","税金","毛利润","利润率"};


        //excel文件名
        String fileName = "利润分析表.xls";

        //sheet名
        String sheetName = "利润分析";
//创建HSSFWorkbook
        HSSFWorkbook wb = profit(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook profit(String sheetName,String []title,List<Profit> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象
            row.createCell(0).setCellValue(list.get(i).getPro());
            row.createCell(1).setCellValue(list.get(i).getShipmentNumbers());
            row.createCell(2).setCellValue(list.get(i).getNumber());
            row.createCell(3).setCellValue(list.get(i).getQyd());
            row.createCell(4).setCellValue(list.get(i).getDzs());
            row.createCell(5).setCellValue(list.get(i).getDzd());
            row.createCell(6).setCellValue(list.get(i).getSr());
            row.createCell(7).setCellValue(list.get(i).getZzc());
            row.createCell(8).setCellValue(list.get(i).getSl());
            row.createCell(9).setCellValue(list.get(i).getSj());
            row.createCell(10).setCellValue(list.get(i).getLr());
            row.createCell(11).setCellValue(list.get(i).getLrl());

        }
        return wb;
    }


    //应收
    public  void  exportreceivable(HttpServletResponse response,  List<Receivable> list){
        //excel标题
        String[] title = {"项目","状态","已开票","下单日期","装运单号","提货单号","起运地",
                "结算地","结算吨位区间","单价","总重量","收货单位","收货详细地址","里程",
                "结算金额(不含税)","税率","结算金额(含税)", "附加费用","总金额(含税)"};


        //excel文件名
        String fileName = "应收报表.xls";

        //sheet名
        String sheetName = "应收";
//创建HSSFWorkbook
        HSSFWorkbook wb = receivable(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook receivable(String sheetName,String []title,List<Receivable> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        //创建内容
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象
            row.createCell(0).setCellValue(list.get(i).getProject());
            if(list.get(i).getStatus().equals("1")){
                row.createCell(1).setCellValue("已创建");
            }else{
                row.createCell(1).setCellValue("已确认");
            }
            if(list.get(i).getReceived().equals("1")){
                row.createCell(2).setCellValue("是");
            }else{
                row.createCell(2).setCellValue("否");
            }
            row.createCell(3).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(4).setCellValue(list.get(i).getShipmentNumbers());
            row.createCell(5).setCellValue(list.get(i).getThdh());
            row.createCell(6).setCellValue(list.get(i).getStart());
            row.createCell(7).setCellValue(list.get(i).getArrive());
            row.createCell(8).setCellValue(list.get(i).getWidth());
            row.createCell(9).setCellValue(list.get(i).getPrice());
            row.createCell(10).setCellValue(list.get(i).getWeight());
            row.createCell(11).setCellValue(list.get(i).getCompany());
            row.createCell(12).setCellValue(list.get(i).getSite());
            row.createCell(13).setCellValue(list.get(i).getMileage());
            row.createCell(14).setCellValue(list.get(i).getSurplus());
            row.createCell(15).setCellValue(list.get(i).getSl());
            row.createCell(16).setCellValue(list.get(i).getHs());
            row.createCell(17).setCellValue(list.get(i).getAd());
            row.createCell(18).setCellValue(list.get(i).getFreight());
        }
        return wb;
    }


    //结算单
    public  void  exportjs(HttpServletResponse response,  List<Js> list){
        //excel标题
        String[] title = {"项目","开票金额","开票日期","受票方","规格","单位","数量","单价","发票号","服务名称","状态","核销金额","未核销金额"};


        //excel文件名
        String fileName = "应收结算单报表.xls";

        //sheet名
        String sheetName = "应收结算单";
//创建HSSFWorkbook
        HSSFWorkbook wb = js(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook js(String sheetName,String []title,List<Js> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        //创建内容
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象
            row.createCell(0).setCellValue(list.get(i).getPro());
            row.createCell(1).setCellValue(list.get(i).getKpje());
            row.createCell(2).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(3).setCellValue(list.get(i).getSpf());
            row.createCell(4).setCellValue(list.get(i).getGg());
            row.createCell(5).setCellValue(list.get(i).getDw());
            row.createCell(6).setCellValue(list.get(i).getSl());
            row.createCell(7).setCellValue(list.get(i).getDj());
            row.createCell(8).setCellValue(list.get(i).getFph());
            row.createCell(9).setCellValue(list.get(i).getFwmc());
            row.createCell(10).setCellValue(list.get(i).getStatus());
            row.createCell(11).setCellValue(list.get(i).getHxje());
            row.createCell(12).setCellValue(list.get(i).getWhxje());


        }
        return wb;
    }




    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"iso-8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
  }
